Data in business applications is often viewed in the form of a spreadsheet. A spreadsheet may be thought of as a “two dimensional” array of data. Each cell in the spreadsheet represents a value of two related entities, or dimensions. For example, one dimension may be time, while a corresponding cross-dimension may be revenue. Many applications, however, may have data, which has more than two dimensions. Business data having more than two dimensions are called multi-dimensional data.
Multi-dimensional data is often used in financial planning, budgeting, and forecasting applications. Examples of common dimensions in such an application include account, time, product, business unit, financial scenarios, and the like. A particular piece of data in such a multi-dimensional data space may be (revenue, February-2005, service, budget, 1000) representing the fact that the data value is 1000 for account=revenue, time=February-2005, business unit=service, and financial scenario=budget.
A common calculation in planning, budgeting, and forecasting applications is to set a high-level goal and then break it down to lower level goals that support the high-level goal. This type of calculation is called allocation. For example, one can set the all-year-2004 revenue goal as $10 million for business unit=service, and financial scenario=budget. This goal can then be broken down to revenue goals for each of the 12 months in 2004. One approach for performing an allocation is to represent the multi-dimensional data in an OLAP (online analytical processing) model such as Microsoft SQL Server Analysis Service® cube, generate Multi-Dimensional eXpression (MDX) query for the allocation, and then execute the query in the Microsoft SQL Server Analysis Service engine.
SQL Server Analysis Service engine may perform poorly for sparse calculations—a calculation is called sparse, if the number of data points need to be calculated is large but most of calculated results are actually zero. The MDX queries generated for allocations tend to be sparse for many of business applications. Therefore, a brute force execution of these queries on an SQL Server Analysis Service engine may result in poor performance.